<?PHP
   /**
    * Script for accessing a database of information for sunspot light data.
    * PHP was chosen because it would not be easy to get the VPN access needed
    * by the Android phones to directly modify and query a SQL database on a
    * remote machine.  This script acts as (yet another) middle man for the
    * remote control of spots with the Android phones.
    *
    * @author Matthew Zizzi (mhzizzi@ncsu.edu)
    */

    /* Database connection constants */
    $DB_USER = 'root';
    $DB_PASS = '';
    $DB_NAME = 'SpotDatabase';

    /**
     * Return a list of all nodes in the database separated by newlines
     * @return <mac>|<state>|<red>|<green>|<blue>\n....
     */
    function getNodes() {
        $res = mysql_query("SELECT * FROM tbl_nodes");
        $output = "";
        while ($row = mysql_fetch_array($res)) {
            $output .= $row['mac']."|".$row['state']."|".$row['red'];
            $output .= "|".$row['green']."|".$row['blue']."\n";
        }
        return $output;
    }

    /**
     * Return a list of all data from a given node with mac address == $mac
     * @return <mac>|<timestamp>|<light>|<state>|<red>|<green>|<blue>\n
     */
    function getData($mac) {
        $query = "SELECT * FROM tbl_data WHERE mac='".mysql_real_escape_string($mac)."'";
        $res = mysql_query($query);
        $output = "";
        while ($row = mysql_fetch_array($res)) {
            $output .= $row['mac']."|".$row['dtime']."|".$row['light']."|";
            $output .= $row['state']."|".$row['red']."|".$row['green']."|";
            $output .= $row['blue']."\n";
        }
        return $output;
    }

    /**
     * Set the state of a spot with mac address = $mac to flashing.
     * Set colors of red, green, and blue to r,g,b, respectively.
     */
    function setState($mac, $r, $g, $b) {
        //Attempt casting input vars to ints and excape the mac address.
        try {
            $mac   = mysql_real_escape_string($mac);
            $r     = (int)$r;
            $g     = (int)$g;
            $b     = (int)$b;
        } catch(Exception $e) {
            return "InvalidParams";
        }
        //Params are valid, execute the sql.
        try {
            $query =  "UPDATE tbl_nodes SET state=2, red={$r},";
            $query .= " green={$g}, blue={$b} WHERE mac='{$mac}'";
            mysql_query($query);
            return "OK";
        } catch(Exception $e) {
            return "Error";
        }
    }

//---------------------------------------------------------------------//
//----------------------- Start Procedural Code -----------------------//
//---------------------------------------------------------------------//

    /* Get database connection */
    mysql_connect('localhost', $DB_USER, $DB_PASS) or die(mysql_error());
    mysql_select_db($DB_NAME) or die(mysql_error());

    /* Get action parameter or set it to "" if it does not exist */
    $action;
    if (array_key_exists('action', $_GET)) {
        $action = $_GET['action'];
    } else {
        $action = "";
    }

    /* Decide what to do base on the action parameter's value */
    if ($action == "") {
        echo "InvalidParams";
        die();
    } else if ($action == "get_nodes") {
        echo getNodes();
        die();
    } else if ($action == "get_data") {
        if (array_key_exists('mac', $_GET)) {
            echo getData($_GET['mac']);
        } else {
            echo "InvalidParams";
        }
        die();
    } else if ($action == "set_node") {
        try {
            $mac = $_GET['mac'];
            $r   = $_GET['r'];
            $g   = $_GET['g'];
            $b   = $_GET['b'];
            echo setState($mac, $r, $g, $b);
        } catch(Exception $e) {
            echo "InvalidParams";
        }  
        die();   
    }
?>
